SMURF_DataMunging
I. Data Proofing: Data Type Standardization
Important Data Munging Notes: - Watch out for date format when reading data in - Tag ID MUST be read in as a character value
Read in current file that contains data that has gone through initial cleaning, including…
- Changing column names to be more “R interpretable”
- Manual check for data types in each column to ensure data type consistency
- quarter: spring, fall, winter, summer
- year: no missing values allowed, if missing values were filled; years are in format YYYY
- ID: should be a derived column… not super critical b/c we can recreate it with the quarter + year columns, have left it in and untouched for now
- collectors: nothing done to this column
- date: no missing values allowed, if missing values were filled; date in format mm/dd/yy
- day_no: describes the sequence of trapping day for each 3-day trapping session; column was derived using date, quarter, year columns (see R code in DataMunging.Rmd)
- x: no missing values allowed, if missing values were filled; must be 0 - 180 in sequence by 20m
- y: no missing values allowed, if missing values were filled; must be 0 - 300
- trap_malfuction: new column created as a consensus from bait_stolen and tripped_trapped columns; yes in either of these columns is a yes for trap malfunction
- trap_malfunction_reason: description of type of trap malfunction, which could extend beyond bait stolen or tripped trap; e.g. trap not placed, trap not found, etc.
- animal: must be y or n, NA values proofed and fixed
- recap: this column is a mess, difficult to proof so not touched
- tag_id: lengthy data proofing below
- genus: manually checked for consistent genera; formatted to title case full spelling
- species: manually checked for consistent species; formatted to lowercase full spelling
- total_weight/bag_weight/animal_weight/body_length/tail_length/ear_length/hindfoot_length: manually chcked for weird values outside reasonable range for that species
- life_stage/sex/repro: manually checked for consistent data types
- other columns (tick columns, hair, fecal, torn ear, escaped): manually checked for consistency but not much else done
To do + species: resolution needed for (1) inconsistent species recorded for same tag and (2) no species recorded How to resolve? Machine learning methods using R package mclust that uses covariates (weight, tail length, ear length, hind foot length, life stage, etc.) to assign species. I have done a first crack at this but need to check these methods with Justin Suraci. Potentially use his dataset as a training data set?
- trap malfunction: likely more trap malfunction occurrence but put in notes since this column was not added to the datasheet til ~2015
- torn ear and escaped: these data are also possibly hidden in the notes/comments since these columns also were not added to the datasheet til ~ 2015 How to resolve? Need manual checking. I did some of this by searching for key words in the comments section. We also need to consider how we might incorporate these sources of error into our analyses or we should at least mention them in the manuscript.
II. Tag ID Data Proofing
smdatN <- read_csv("/Users/tinacheng/Dropbox/SMURF_MS/Data/FERP_RodentData/DataMungingFiles/smdatN_fixed.csv",
col_types = cols(tag_id = col_character(),
no_tag_reason = col_character()))II. Fixing tags - Notes
- Fix duplicate tags
- remove corrections (1000 and 2000 previously added to number)
- identify which tags are duplicated by date distance (# days between tag observations)
- Fix made:
- made new recap column by date distance (animal was not a recap if caught over 2 yrs later)
- FE prefix
- Add FE prefix to newly tagged animals after Feb 2, 2016
- Add FE prefix to recap caught after Feb 2, 2016 and whose date distance is more than a year
- To do:
- check tags with no prefix whose date distance is within a year
- Other messy shit
- there are some animals marked with sharpie, I have done my best to make these IDs unique at least to the trapping session
- there are definitely errors in the tag (transcription error or read incorrectly) that could lead to misidentifcation of a duplicate tag; this would result in an inflated number of newly tagged animals
- in the past, tags were sometimes read incorrectly if read upside down; e.g. 99 = 66; in 2014, I did my best to correct these by going through them with a high school intern, but more of these errors may exist
- How to fix:
- Calculate % tag misread/mistranscribed by proofing 100 random tags, maybe more, but start with 100
#Figure out when FE0 tags started being used
smFE <- smdatN %>%
filter(grepl("FE", tag_id))
FE_startdate = min(smFE$date)
#FE_startdate
#started tagging with FE IDs in summer 20161. Strip down tag numbers
- standardizes numbers to 4 digits
- standardizes prefix to uppercase
- replace NA with blanks
smTags1 <- smdatN %>%
#format tag no and tag prefix, then paste together again
mutate(tag_no = str_extract(tag_id, "(\\d)+"), #extract number from tag_id
tag_no = str_pad(tag_no, 4, pad = "0"), #format to ####
#create new column called tag_no_new for reformatted tags
tag_no_new = substring(tag_no, 2, 4), #break down into just last 3 digits (removing 1000 or 2000 manually assigned)
tag_no_new = str_pad(tag_no_new, 4, pad = "0"), #format back into ####
#extract tag prefix
tag_id = toupper(tag_id), #first make sure they are all uppercase
tag_id = str_replace_all(tag_id, "NA", ""), #there were a couple "NA" characters that needed to get replaced with nothing
tag_prefix = str_extract(tag_id, "[A-Z]+"),
tag_id_new = ifelse(is.na(tag_prefix), tag_no_new, paste(tag_prefix, tag_no_new, sep = ""))) #extract out tag prefixsmTags1_display <- smTags1 %>% dplyr::select(quarter, year, date, day_no, x, y, animal, recap, tag_id, tag_no, tag_no_new, tag_prefix, tag_id_new)
DT::datatable(smTags1_display, filter = "top")4. Identify duplicate tags
4a. Identify new tagging events
First create a new column to identify newly caught animals based on “date distance” (# days since tag was last recorded). I did this because the recap column that should identify when new animals are caught is not reliable. Tags are recorded as “new” if…
It is the earliest record of the tag (first time tag is ever recorded)
The tag was recorded more than a year (date distance >= 730 days) from previous tag observation
Assumption: If an animal has not been recaught within 2 years, it is assumed dead or to have emigrated from the population. Thus, new observations of that tag are assumed to be a new animal.
ddFUN <- function(dd){
smTags3 %>%
#Fix tags that are duplicate tags
arrange(tag_id_new, date) %>%
group_by(tag_id_new) %>%
#create new column to identify when a new tagging event has occurred, defined by first tag event (is.na(date_dist)) and if it has been more than one year since animal was tagged compared to last tagging event
mutate(new_tag = ifelse(animal == "y" & is.na(date_dist), "y",
ifelse(animal == "y" & date_dist >= dd, "y",
ifelse(animal == "y" & date_dist < dd, "n", NA))),
dist_day = dd) %>%
#########
ungroup() %>%
arrange(new_tag, tag_id_new, date) %>% #arrange data to get new tag events together
group_by(new_tag, tag_id_new) %>% #group data
mutate(n = ifelse(new_tag == "y", n(), NA))
}
dd = c(365, 730, 1096)
smTags4_dd <- map_df(dd, ddFUN)smTags4_dd %>%
filter(!is.na(tag_id_new)) %>%
ggplot() +
geom_bar(aes(x = n, fill = as.factor(dist_day)), position = "dodge", alpha = 0.4, color = "grey") +
xlab("# times tag used") +
scale_fill_manual(labels = c("1 yr", "2 yrs", "3 yrs"), values = c("red", "orange", "yellow")) +
labs(fill = "date distance <= ...") +
ggtitle("Identifying duplicate tags based on date distance")
The number of newly identified tagging events varies based on the cutoff in date distance used. In other words, if a tag is not recorded for 1 year and then recorded again, is that tag a new but duplicate tag or was the tag just not observed for 1 year? What about 2 years? 3 years? Depending on our cutoff, the number of new but duplicate tags varies. if we choose too short of a time, we are biasing ourselves towards more new duplicate tags (higher turnover of animals & higher recapture rate). But if we choose too high a value, we are biasing ourselves towards longer-lived and more sporadically caught animals (lower turnover of animals and lower recapture rate). What number to use? It’s impossible to know without more info…
I will choose the intermediary value, 2 years or 730 days.
ddc = 730 #date distance cutoff is 730, or 2 years
smTags4 <- smTags3 %>%
#Fix tags that are duplicate tags
arrange(tag_id_new, date) %>%
group_by(tag_id_new) %>%
#create new column to identify when a new tagging event has occurred, defined by first tag event (is.na(date_dist)) and if it has been more than one year since animal was tagged compared to last tagging event
mutate(new_tag = ifelse(animal == "y" & is.na(date_dist), "y",
ifelse(animal == "y" & date_dist >= ddc, "y",
ifelse(animal == "y" & date_dist < ddc, "n", NA))))smTags4_display <- smTags4 %>%
dplyr::select(quarter, year, date, day_no, x, y, animal, recap, tag_id_new, date_dist, trap_dist, new_tag)
DT::datatable(smTags4_display, filter = "top")III. To Do:
1. Misread/Mistranscribed tags
There are still tag ID’s that are incorrect from being misread or mistranscribed. It is too time consuming to proof every single tag. Thus, we will choose a random subset of ID’s and calculate a rate at which tags were misread/mistranscribed.
smTags6 <- smTags5 %>%
arrange(tag_id_new, date) %>%
#recalculate date_dist and trap_dist
group_by(tag_id_new) %>%
mutate(date_dist = date - lag(date), #number of days between date and previous tag date
trap_dist = sqrt((abs(x - lag(x))^2) + (abs(y - lag(y))^2))) #distance in meters from previous tag trap)
#write_csv(smTags6, "/Users/tinacheng/Dropbox/SMURF_MS/Data/FERP_RodentData/DataMungingFiles/SMURF_Master_Tags.csv")smTags6 %>%
filter(species %in% c("californicus", "truei")) %>%
filter(date_dist < 400) %>%
ggplot() +
geom_histogram(aes(x = trap_dist, fill = species), alpha = 0.4) +
xlab("Distance (m): trap at timepoint (t-1) - trap at timepoint (t)") +
ylab("# of trapping events")
p1 <- smTags6 %>%
filter(species %in% c("californicus", "truei")) %>%
#filter(date_dist < 400) %>%
ggplot() +
geom_vline(xintercept = 730, color = "grey") +
geom_histogram(aes(x = date_dist, fill = species), alpha = 0.4) +
xlab("Distance (m): trap at timepoint (t-1) - trap at timepoint (t)") +
ylab("# of trapping events")
p2 <- smTags6 %>%
filter(species %in% c("californicus", "truei")) %>%
filter(date_dist <= 730) %>%
ggplot() +
geom_histogram(aes(x = date_dist, fill = species), alpha = 0.4) +
xlab("Distance (m): trap at timepoint (t-1) - trap at timepoint (t)") +
ylab("# of trapping events")
grid.arrange(p1, p2, ncol = 1)
Pick out random tag IDs to proof
First set of 100 tags to proof
utags <- unique(smTags6$tag_id_new)
tags2check <- sample(utags, size = 100, replace = F)
tags2check## [1] "0290" "FE0039" "0537" "2311" "0975" "0204" "FE0099"
## [8] "FE0126" "1978" "FE0430" "0885" "FT0470" "FT0340" "1079"
## [15] "FE0354" "0615" "0683" "0966" "0565" "0426" "0647"
## [22] "FE0030" "2398" "0098" "FE0990" "1008" "FE0160" "1191"
## [29] "2380" "0331" "0039" "0629" "0597" "FE0176" "0937"
## [36] "0701" "1303" "FE0331" "0830" "0194" "1266" "0844"
## [43] "0448" "FE0128" "0953" "FE0303" "FT0112" "0318" "0123"
## [50] "FE0377" "0167" "FE0317" "FE0045" "0601" "0370" "FE0383"
## [57] "FE0302" "FE0389" "0986" "0170" "0902" "FE0388" "0489"
## [64] "2386" "0308" "FE0456" "FE0103" "0810" "0155" "0969"
## [71] "0568" "FE0170" "0596" "0386" "0272" "1249" "0965"
## [78] "1179" "FE0143" "FE0097" "FE0017" "FE0247" "FE0290" "0109"
## [85] "0086" "0982" "0954" "1022" "FE0078" "0878" "0252"
## [92] "FE0080" "FE0196" "0906" "1402" "1398" "FE0426" "0265"
## [99] "0299" "1327"3. Check tag ID’s not recorded but where an animal was caught
Table 1. Summary of tagging events by species that have NO tag recorded. These events are often due to an escaped animal or in the case of non-Peromyscus species, the animal was not tagged.
#animals caught but with no tag read or animal not tagged
notag <- smTags6 %>%
filter(animal == "y") %>%
filter(is.na(tag_id_new)) %>%
mutate(Species = ifelse(!is.na(genus) & is.na(species), paste(genus, "sp."),
ifelse(is.na(genus), "*no sp recorded",
ifelse(genus == "Sorex", "Sorex sp.",
ifelse(genus == "Neotoma", "Neotoma fuscipes",
ifelse(genus == "Microtus", "Microtus californiucs",
ifelse(genus == "Reithrotodontomys", "Reithrodontomys megalotis",
ifelse(genus == "Peromyscus", paste(genus, species), "*no sp recorded")))))))) %>%
group_by(Species) %>%
summarise(`# tagging events w/ no tag recorded` = n())
kable(notag, format = "markdown", align = c('c'))| Species | # tagging events w/ no tag recorded |
|---|---|
| *no sp recorded | 116 |
| Microtus californiucs | 4 |
| Neotoma fuscipes | 30 |
| Peromyscus californicus | 43 |
| Peromyscus sp. | 9 |
| Peromyscus truei | 78 |
| Sorex sp. | 181 |
noPE <- smTags6 %>%
filter(genus == "Peromyscus") %>%
nrow()